Micron Document
`:top
In `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f, a `!window function`! or `!analytic function`!`:cite-ref-1-1-0[`F5bf`_`[1`#cite-note-1-1]`_`f] is a function which uses values from one or multiple `F33f`_`[rows`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Row_(database)]`_`f to return a value for each row. (This contrasts with an `F33f`_`[aggregate function`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Aggregate_function]`_`f, which returns a single value for multiple rows.) Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.`:cite-ref-2[`F5bf`_`[2`#cite-note-2]`_`f]

>>Contents

• `F0af`_`[Example`#example]`_`f
• `F0af`_`[Syntax`#syntax]`_`f
• `F0af`_`[Example`#example]`_`f
• `F0af`_`[History`#history]`_`f
• `F0af`_`[See also`#see-also]`_`f
• `F0af`_`[References`#references]`_`f

-─

>>Example

As an example, here is a query which uses a window function to compare the salary of each employee with the average salary of their department (example from the `F33f`_`[PostgreSQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PostgreSQL]`_`f documentation):`:cite-ref-3[`F5bf`_`[3`#cite-note-3]`_`f]

`B100`F9d9SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;`f`b

Output:

`B100`F9d9 depname | empno | salary | avg`f`b
`B100`F9d9----------+-------+--------+----------------------`f`b
`B100`F9d9develop | 11 | 5200 | 5020.0000000000000000`f`b
`B100`F9d9develop | 7 | 4200 | 5020.0000000000000000`f`b
`B100`F9d9develop | 9 | 4500 | 5020.0000000000000000`f`b
`B100`F9d9develop | 8 | 6000 | 5020.0000000000000000`f`b
`B100`F9d9develop | 10 | 5200 | 5020.0000000000000000`f`b
`B100`F9d9personnel | 5 | 3500 | 3700.0000000000000000`f`b
`B100`F9d9personnel | 2 | 3900 | 3700.0000000000000000`f`b
`B100`F9d9sales | 3 | 4800 | 4866.6666666666666667`f`b
`B100`F9d9sales | 1 | 5000 | 4866.6666666666666667`f`b
`B100`F9d9sales | 4 | 4800 | 4866.6666666666666667`f`b
`B100`F9d9(10 rows)`f`b

The `B100`F9d9PARTITION BY`f`b clause groups rows into partitions, and the function is applied to each partition separately. If the `B100`F9d9PARTITION BY`f`b clause is omitted (such as with an empty `B100`F9d9OVER()`f`b clause), then the entire `F33f`_`[result set`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Result_set]`_`f is treated as a single partition.`:cite-ref-0-4-0[`F5bf`_`[4`#cite-note-0-4]`_`f] For this query, the average salary reported would be the average taken over all rows.

Window functions are evaluated after aggregation (after the `F33f`_`[GROUP BY`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Group_by_(SQL)]`_`f clause and non-window aggregate functions, for example).`:cite-ref-1-1-1[`F5bf`_`[1`#cite-note-1-1]`_`f]

>>Syntax

According to the PostgreSQL documentation, a window function has the syntax of one of the following:`:cite-ref-0-4-1[`F5bf`_`[4`#cite-note-0-4]`_`f]

`B100`F9d9function_name ([expression [, expression ... ]]) OVER window_name`f`b
`B100`F9d9function_name ([expression [, expression ... ]]) OVER ( window_definition )`f`b
`B100`F9d9function_name ( * ) OVER window_name`f`b
`B100`F9d9function_name ( * ) OVER ( window_definition )`f`b

where `B100`F9d9window_definition`f`b has syntax:

`B100`F9d9[ existing_window_name ]`f`b
`B100`F9d9[ PARTITION BY expression [, ...] ]`f`b
`B100`F9d9[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]`f`b
`B100`F9d9[ frame_clause ]`f`b

`B100`F9d9frame_clause`f`b has the syntax of one of the following:

`B100`F9d9{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]`f`b
`B100`F9d9{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]`f`b

`B100`F9d9frame_start`f`b and `B100`F9d9frame_end`f`b can be `B100`F9d9UNBOUNDED PRECEDING`f`b, `B100`F9d9offset PRECEDING`f`b, `B100`F9d9CURRENT ROW`f`b, `B100`F9d9offset FOLLOWING`f`b, or `B100`F9d9UNBOUNDED FOLLOWING`f`b. `B100`F9d9frame_exclusion`f`b can be `B100`F9d9EXCLUDE CURRENT ROW`f`b, `B100`F9d9EXCLUDE GROUP`f`b, `B100`F9d9EXCLUDE TIES`f`b, or `B100`F9d9EXCLUDE NO OTHERS`f`b.

`B100`F9d9expression`f`b refers to any expression that does not contain a call to a window function.

Notation:

• Brackets [] indicate optional clauses
• Curly braces {} indicate a set of different possible options, with each option delimited by a vertical bar |

>>Example

Window functions allow access to data in the records right before and after the current record.`:cite-ref-5[`F5bf`_`[5`#cite-note-5]`_`f]`:cite-ref-6[`F5bf`_`[6`#cite-note-6]`_`f]`:cite-ref-7[`F5bf`_`[7`#cite-note-7]`_`f]`:cite-ref-8[`F5bf`_`[8`#cite-note-8]`_`f] A window function defines a `*frame`* or `*window`* of rows with a given length around the current row, and performs a calculation across the set of data in the window.`:cite-ref-9[`F5bf`_`[9`#cite-note-9]`_`f]`:cite-ref-10[`F5bf`_`[10`#cite-note-10]`_`f]

`B100`F9d9 NAME |`f`b
`B100`F9d9------------`f`b
`B100`F9d9 Aaron| <-- Preceding (unbounded)`f`b
`B100`F9d9 Andrew|`f`b
`B100`F9d9 Amelia|`f`b
`B100`F9d9 James|`f`b
`B100`F9d9 Jill|`f`b
`B100`F9d9 Johnny| <-- 1st preceding row`f`b
`B100`F9d9 Michael| <-- Current row`f`b
`B100`F9d9 Nick| <-- 1st following row`f`b
`B100`F9d9 Ophelia|`f`b
`B100`F9d9 Zach| <-- Following (unbounded)`f`b

In the above table, the next query extracts for each row the values of a window with one preceding and one following row:

`B100`F9d9 SELECT`f`b
`B100`F9d9 LAG(name, 1)`f`b
`B100`F9d9 OVER(ORDER BY name) "prev",`f`b
`B100`F9d9 name,`f`b
`B100`F9d9 LEAD(name, 1)`f`b
`B100`F9d9 OVER(ORDER BY name) "next"`f`b
`B100`F9d9 FROM people`f`b
`B100`F9d9 ORDER BY name`f`b

The result query contains the following values:

`B100`F9d9| PREV | NAME | NEXT |`f`b
`B100`F9d9|----------|----------|----------|`f`b
`B100`F9d9| (null)| Aaron| Andrew|`f`b
`B100`F9d9| Aaron| Andrew| Amelia|`f`b
`B100`F9d9| Andrew| Amelia| James|`f`b
`B100`F9d9| Amelia| James| Jill|`f`b
`B100`F9d9| James| Jill| Johnny|`f`b
`B100`F9d9| Jill| Johnny| Michael|`f`b
`B100`F9d9| Johnny| Michael| Nick|`f`b
`B100`F9d9| Michael| Nick| Ophelia|`f`b
`B100`F9d9| Nick| Ophelia| Zach|`f`b
`B100`F9d9| Ophelia| Zach| (null)|`f`b

>>History

Window functions were incorporated into the `F33f`_`[SQL:2003`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL:2003]`_`f standard and had functionality expanded in later specifications.`:cite-ref-11[`F5bf`_`[11`#cite-note-11]`_`f]

Support for particular database implementations was added as follows:

• `F33f`_`[Oracle`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Oracle_Database]`_`f - version 8.1.6 in 2000.`:cite-ref-12[`F5bf`_`[12`#cite-note-12]`_`f]`:cite-ref-13[`F5bf`_`[13`#cite-note-13]`_`f]
• `F33f`_`[PostgreSQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PostgreSQL]`_`f - version 8.4 in 2009.`:cite-ref-14[`F5bf`_`[14`#cite-note-14]`_`f]
• `F33f`_`[MySQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=MySQL]`_`f - version 8 in 2018.`:cite-ref-15[`F5bf`_`[15`#cite-note-15]`_`f]`:cite-ref-16[`F5bf`_`[16`#cite-note-16]`_`f]
• `F33f`_`[MariaDB`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=MariaDB]`_`f - version 10.2 in 2016.`:cite-ref-17[`F5bf`_`[17`#cite-note-17]`_`f]
• `F33f`_`[SQLite`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQLite]`_`f - release 3.25.0 in 2018.`:cite-ref-18[`F5bf`_`[18`#cite-note-18]`_`f]

>>See also

• `F33f`_`[Select (SQL) § Limiting result rows`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Select_(SQL)]`_`f

>>References

`:cite-note-1-1`!1.`! `F0af`_`[↑`#cite-ref-1-1-0]`_`f "Analytic function concepts in Standard SQL | BigQuery". `*Google Cloud`*. Retrieved 2021-03-23.
`:cite-note-2`!2.`! `F0af`_`[↑`#cite-ref-2]`_`f "Window Functions". `*sqlite.org`*. Retrieved 2021-03-23.
`:cite-note-3`!3.`! `F0af`_`[↑`#cite-ref-3]`_`f "3.5. Window Functions". `*PostgreSQL Documentation`*. 2021-02-11. Retrieved 2021-03-23.
`:cite-note-0-4`!4.`! `F0af`_`[↑`#cite-ref-0-4-0]`_`f "4.2. Value Expressions". `*PostgreSQL Documentation`*. 2021-02-11. Retrieved 2021-03-23.
`:cite-note-5`!5.`! `F0af`_`[↑`#cite-ref-5]`_`f `:citerefleiskundhikanjanakemperneumann2015`aLeis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (June 2015). "Efficient Processing of Window Functions in Analytical SQL Queries". `*Proc. VLDB Endow`*. `!8`! (10): 1058–1069. `F33f`_`[doi`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Doi_(identifier)]`_`f:10.14778/2794367.2794375. `F33f`_`[ISSN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISSN_(identifier)]`_`f 2150-8097.
`:cite-note-6`!6.`! `F0af`_`[↑`#cite-ref-6]`_`f `:citerefcaochanlitan2012`aCao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (July 2012). "Optimization of Analytic Window Functions". `*Proc. VLDB Endow`*. `!5`! (11): 1244–1255. `F33f`_`[arXiv`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ArXiv_(identifier)]`_`f:1208.0086. `F33f`_`[doi`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Doi_(identifier)]`_`f:10.14778/2350229.2350243. `F33f`_`[ISSN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISSN_(identifier)]`_`f 2150-8097.
`:cite-note-7`!7.`! `F0af`_`[↑`#cite-ref-7]`_`f "Probably the Coolest SQL Feature: Window Functions". `*Java, SQL and jOOQ`*. 2013-11-03. Retrieved 2017-09-26.
`:cite-note-8`!8.`! `F0af`_`[↑`#cite-ref-8]`_`f "Window Functions in SQL - Simple Talk". `*Simple Talk`*. 2013-10-31. Retrieved 2017-09-26.
`:cite-note-9`!9.`! `F0af`_`[↑`#cite-ref-9]`_`f "SQL Window Functions Introduction". `*Apache Drill`*.
`:cite-note-10`!10.`! `F0af`_`[↑`#cite-ref-10]`_`f "PostgreSQL: Documentation: Window Functions". `*www.postgresql.org`*. Retrieved 2020-04-04.
`:cite-note-11`!11.`! `F0af`_`[↑`#cite-ref-11]`_`f "Window Functions Overview". `*MariaDB KnowledgeBase`*. Retrieved 2021-03-23.
`:cite-note-12`!12.`! `F0af`_`[↑`#cite-ref-12]`_`f "Oracle 8i Release 2 (8.1.6) New Features". `*www.oracle.com`*. Retrieved 2025-01-23.
`:cite-note-13`!13.`! `F0af`_`[↑`#cite-ref-13]`_`f "Analytic Functions in Oracle 8i" (PDF). `*www.stanford.edu`*. Retrieved 2025-01-23.
`:cite-note-14`!14.`! `F0af`_`[↑`#cite-ref-14]`_`f "PostgreSQL Release 8.4". `*www.postgresql.org`*. 24 July 2014. Retrieved 2024-03-10.
`:cite-note-15`!15.`! `F0af`_`[↑`#cite-ref-15]`_`f "MySQL :: What's New in MySQL 8.0? (Generally Available)". `*dev.mysql.com`*. Retrieved 2022-11-21.
`:cite-note-16`!16.`! `F0af`_`[↑`#cite-ref-16]`_`f "MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax". `*dev.mysql.com`*.
`:cite-note-17`!17.`! `F0af`_`[↑`#cite-ref-17]`_`f "MariaDB 10.2.0 Release Notes". `*mariadb.com`*. Retrieved 2024-03-10.
`:cite-note-18`!18.`! `F0af`_`[↑`#cite-ref-18]`_`f "SQLite Release 3.25.0 On 2018-09-15". `*www.sqlite.org`*. Retrieved 5 February 2025.

`c`F0af`_`[↑ Back to top`#top]`_`f`a